﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace SiteUnity.Layers.Core.Connection.Database
{
    public class MssqlDatabase
    {
        private string connectionString
        { get; set; }

        private SqlConnection connection
        { get; set; }
 
        private SqlCommand command
        { get; set; }

        //public MssqlDatabase()
        //{
        //    connection = new SqlConnection(SettingHelper.GetAppSetting("ConnectionString"));
        //    command = new SqlCommand();
        //}

        public MssqlDatabase(string connectionstring)
        {
            connection = new SqlConnection(connectionstring);
            command = new SqlCommand();
        }

        private bool open()
        {
            if (connection.State != ConnectionState.Open)
                try
                {
                    connection.Open();
                    return true;
                }
                catch
                {
                    return false;
                }
            return true;
        }

        public bool close()
        {
            if (connection.State != ConnectionState.Closed)
                try
                {
                    command.Dispose();
                    connection.Close();
                    return true;
                }
                catch
                {
                    return false;
                }
            return true;
        }

        public void addParameter(string key, object value)
        {
            command.Parameters.Add(new SqlParameter(key, value));
        }

        public void clear()
        {
            if (command.Parameters.Count!=0)
                command.Parameters.Clear();
        }

        public DataTable executeQuery(string fStoredProcedure)
        {
            open();
            command.Connection = connection;
            command.CommandText = fStoredProcedure;
            command.CommandType = CommandType.StoredProcedure;

            DataSet dataset = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(dataset);

            return dataset.Tables[0];
        }

        public int executeNonQuery(string fStoredProcedure)
        {
            open();
            command.Connection = connection;
            command.CommandText = fStoredProcedure;
            command.CommandType = CommandType.StoredProcedure;
 
            return command.ExecuteNonQuery();
        }

        public string executeScaler(string fStoredProcedure)
        {
            open();
            command.Connection = connection;
            command.CommandText = fStoredProcedure;
            command.CommandType = CommandType.StoredProcedure;

            return command.ExecuteScalar().ToString();
        }
    }
}
